package packageInputData;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;

/**
* @author LABIB TAKOUTI		 takouti.labib[AT]gmail.com
* @version 1.0 on 06/2012
*/

public class DbConnection {
	
	//Parameters for the database connection
	private static String login="";		
	private static String password="";	
	private static String port="";		
	
	private static String dbName="";		
	
	private static String jdbcDriver=""; 
	
	private static String dbAdress="";
	
	private static String jdbcURL=""; 	
	
	/**
	* function to make the get the the wanted string from String line entered in parameter
	* @param 			strLine	String, the data we seek 
	* @return			String		
	*/
	public static String getParam(String strLine)
	{
		String param="";
		String [] data;
		
		data=strLine.split("\t");
 		if (data.length==1)
 			data=strLine.split(" ");
 		
 		for(int i=0;i<data.length;i++)
 			if (!data[i].isEmpty())
 				param=data[i].toString();
		
		return param;
		
	}
	/**
	* function to make the get the connexion paramaters from file
	* @return			void		
	*/
	public static void getConnexionParam()
	{
		String file="connexionParam.txt";
		try{
		  	// Open the file that is the first 
		  	// command line parameter
		  	FileInputStream fstream = new FileInputStream(file);
		  	// Get the object of DataInputStream
		  	DataInputStream in = new DataInputStream(fstream);
		  	BufferedReader br = new BufferedReader(new InputStreamReader(in));			  	
		  	String strLine;
		 	while ((strLine = br.readLine()) != null)   			  	
		  	{
		 		if (strLine.contains("login"))
		 			login=getParam(strLine);
		 		if (strLine.contains("password"))
		 			password=getParam(strLine);
		 		if (strLine.contains("port"))
		 			port=getParam(strLine);
		 		if (strLine.contains("dbName"))
		 			dbName=getParam(strLine);
		 		if (strLine.contains("dbAdress"))
		 			dbAdress=getParam(strLine);
		 		if (strLine.contains("jdbcDriver"))
		 			jdbcDriver=getParam(strLine);
		 		
		 		jdbcURL 	= "jdbc:postgresql://"+dbAdress +":"+
						port +
						"/" +
						dbName;		 		
		 		
		 		
		 			 		
		  	}
		 		 	
		}catch (Exception e){
			//Catch exception if any
		 	 System.err.println("Error: " + e.getMessage());
		 	 }
		//System.out.println(port);
	}
	
	/**
	* function to make the connection to the database 
	* @return			Connection		reference to the made connection (or null, if fail connection)
	*/
	public static Connection connectDB()
	{
		//getConnexionParam();
		
		//Initialise connection
  		Connection conn = null;  		
  		//load Driver
  		boolean connect;
  			try {
			  			connect=DbUtils.loadDriver(jdbcDriver);
			  			
			  			if(!connect){
					  		      //no driver, we stop
					  		      System.out.println("Driver absent.");
					  		      System.exit(0) ;
			  			}
			  			//connection to database
			  			conn = DriverManager.getConnection(jdbcURL, login,password);
			  			
			  			
  			} catch (Exception e) 
  			{
  				e.printStackTrace();	
  			}
  			
  			return conn;		
	}
	
	/**
	* function 
	* to connect to the database, and gets the last occurrence of data from the table in parameter
	* @param 			data	String, the data we seek
	* @param			table	String, the table we take the data from
	* @return			Object		the result of the query
	*/
	@SuppressWarnings("rawtypes")
	public static Object getFromDB(String data ,String table ){
		//connect to the database
				Connection conn=DbConnection.connectDB();					  			
			  	QueryRunner qRunner = new QueryRunner();
				List list1 = new ArrayList();
				
				String req= "select "+data+" from "+table;
				try {
					list1 =(List) qRunner.query(conn,
												req,
												new ArrayListHandler());
					} catch (SQLException e2) {
					e2.printStackTrace();
					}
				//close connection
				DbUtils.closeQuietly(conn);
				if(list1.size()>0)
				{
						Object res[] = (Object[]) list1.get(list1.size()-1);
						return res[0];
				}else 
					return null;
		
	}
	/**
	* function 
	* to connect to the database, and gets the last occurrence of data from the table in parameter
	* @param 			data	String, the data we seek
	* @param			table	String, the table we take the data from
	* @return			Object		the result of the query
	*/
	@SuppressWarnings({ "rawtypes", "deprecation" })
	public static Object getFromDB(String data ,String table,String column, String condition ){
		//connect to the database
				Connection conn=DbConnection.connectDB();					  			
			  	QueryRunner qRunner = new QueryRunner();
				List list1 = new ArrayList();
				
				String req= "select "+
							data+" from "+
							table+" where "+
							column+" = ?";
				
				try {
					list1 =(List) qRunner.query(conn,
												req,
												condition,
												new ArrayListHandler());
					} catch (SQLException e2) {
					e2.printStackTrace();
					}
				//close connection
				DbUtils.closeQuietly(conn);
				if(list1.size()>0)
				{
						Object res[] = (Object[]) list1.get(list1.size()-1);
						return res[0];
				}else 
					return null;
	}

	/**
	* function 
	* to connect to the database, and gets the last occurrence of data from the table in parameter
	* @param 			data	String, the data we seek
	* @param			table	String, the table we take the data from
	* @return			Object		the result of the query
	*/
	@SuppressWarnings({ "rawtypes", "deprecation" })
	public static Object getFromDB(String data ,String table,String column, int condition ){
		//connect to the database
				Connection conn=DbConnection.connectDB();					  			
			  	QueryRunner qRunner = new QueryRunner();
				List list1 = new ArrayList();
				
				String req= "select "+
							data+" from "+
							table+" where "+
							column+" = ?";
				
				try {
					list1 =(List) qRunner.query(conn,
												req,
												condition,
												new ArrayListHandler());
					} catch (SQLException e2) {
					e2.printStackTrace();
					}
				//close connection
				DbUtils.closeQuietly(conn);
				if(list1.size()>0)
				{
						Object res[] = (Object[]) list1.get(list1.size()-1);
						return res[0];
				}else 
					return null;
	}
	/**
	* function 
	* to connect to the database, and gets the last occurrence of data from the table in parameter
	* @param 			sta_id	
	* @return			String		the result of the query
	*/
	@SuppressWarnings({ "rawtypes", "deprecation" })
	public static String getCodeStationByID(int sta_id)
	{
		//connect to the database
		Connection conn=DbConnection.connectDB();					  			
	  	QueryRunner qRunner = new QueryRunner();
		List list1 = new ArrayList();
		
		String req= "select code from station_coord where sta_id=? ";
		try {
			list1 =(List) qRunner.query(conn,
										req,
										sta_id,
										new ArrayListHandler());
			} catch (SQLException e2) {
			e2.printStackTrace();
			}
		//close connection
		DbUtils.closeQuietly(conn);
		if(list1.size()>0)
		{
				Object res[] = (Object[]) list1.get(list1.size()-1);
				return res[0].toString();
		}else 
			return "";
	}
	
	/**
	* function 
	* to connect to the database and clear the table given in parametere
	* @param 			table	String	
	* @return			void		
	*/
	
	public static void clearTable(String table)
	{
				//connect to the database
				Connection conn=DbConnection.connectDB();					  			
			  	QueryRunner qRunner = new QueryRunner();
			  	
			  	String req= "DELETE FROM "+table;
				try {
					qRunner.query(conn,req,new ArrayListHandler());
					} catch (SQLException e2) {
					//e2.printStackTrace();
					}			
				
				/*
				String sequence=table+"_id_seq";
				req="SELECT setval('"+sequence+"',1)";
				try {
					qRunner.query(conn,req,new ArrayListHandler());
					} catch (SQLException e2) {
					//e2.printStackTrace();
					}
					*/
				
				//close connection
				DbUtils.closeQuietly(conn);
				System.out.println("supression done table: "+table);
	}
	/**
	* function 
	* to connect to the database and clear all generated data from DB
	* @return			void		
	*/
	public static void clearGeneratedData()
	{
		
		clearTable("vcopsepok");
		clearTable("epok");
		clearTable("interp_iwv");
		clearTable("iwv");
		clearTable("prep_iwv_graph");
		clearTable("ptu");
		clearTable("ptu2");
		clearTable("siwv");
		clearTable("sxyz");
		clearTable("tomo");
		clearTable("zhd");
		clearTable("ztd");
		clearTable("ztd_temp");
		clearTable("ztdhmf");
		clearTable("zwd");
		clearTable("altitude_lay");		
		clearTable("param_file_2");
		clearTable("param_file_1");
		clearTable("rays_tomo");
		clearTable("res_tomo");
		
		
	}
	/**
	* function 
	* to connect to the database and clear all generated data log from DB
	* @return			void		
	*/
	public static void clearGeneratedDataLog()
	{
		
		clearTable("vcopsepok_log");
		clearTable("epok_log");
		clearTable("interp_iwv_log");
		clearTable("iwv_log");
		clearTable("prep_iwv_graph_log");
		clearTable("ptu_log");
		clearTable("ptu2_log");
		clearTable("siwv_log");
		clearTable("sxyz_log");
		clearTable("tomo_log");
		clearTable("zhd_log");
		clearTable("ztd_log");
		clearTable("ztd_temp_log");
		clearTable("ztdhmf_log");
		clearTable("zwd_log");
		clearTable("altitude_lay_log");		
		clearTable("param_file_2_log");
		clearTable("param_file_1_log");
		clearTable("rays_tomo_log");
		clearTable("res_tomo_log");
		
		
	}
	/**
	* function 
	* to connect to the database and copy all data to log
	* @return			void		
	*/
	
	public static void copyToLog()
	{
		copyTableToLog("vcopsepok");
		copyTableToLog("epok");
		copyTableToLog("interp_iwv");
		copyTableToLog("iwv");
		copyTableToLog("prep_iwv_graph");
		copyTableToLog("ptu");
		copyTableToLog("ptu2");
		copyTableToLog("siwv");
		copyTableToLog("sxyz");
		copyTableToLog("tomo");
		copyTableToLog("zhd");
		copyTableToLog("ztd");
		copyTableToLog("ztd_temp");
		copyTableToLog("ztdhmf");
		copyTableToLog("zwd");
		copyTableToLog("altitude_lay");		
		copyTableToLog("param_file_2");
		copyTableToLog("param_file_1");
		copyTableToLog("rays_tomo");
		copyTableToLog("res_tomo");
	}
	
	public static void clearInputData()
	{
		clearTable("intrelief_matrix");
		
		clearTable("igs_data");
		clearTable("igs_times");
		clearTable("igs");
		
		
		clearTable("slant_data");
		clearTable("station_coord");
		clearTable("satelite_used");
		clearTable("slant_delay");
		
		clearTable("front");
		clearTable("atmo_stan_pro");
		clearTable("rinex");
		clearTable("param");
		clearTable("sites_gps");
		clearTable("sites_all");
		
	}
	public static void copyTableToLog(String table)
	{
		//connect to the database
		Connection conn=DbConnection.connectDB();					  			
	  	QueryRunner qRunner = new QueryRunner();
	  	
	  	//Connection connLog=DbConnection.connectDBLog();					  			
	  	//QueryRunner qRunnerLog = new QueryRunner();
		
	  	String req;//= "Select * into "+table +"_log from "+table;
	  	req="	INSERT INTO "+table+"_log " +
	  			"SELECT * " +
	  			"FROM "+table;
		try {
			qRunner.query(conn,req,new ArrayListHandler());
			} catch (SQLException e2) {
			//e2.printStackTrace();
			}
		
		
	}
	
	public static void main (String args[])
	{
		//getConnexionParam();
		//clearInputData();
		//clearGeneratedData();
		//clearGeneratedDataLog();
	}

}
